﻿#region Using Region

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data;
using ICE.Vacations.Helper;
using ICE.Vacations.Helper.Services;

#endregion

namespace ICE.Vacations.DataBase
{
    public class SQLDataClass
    {
        private SqlConnection sqlConnection { get; set; }

        public void OpenConnection()
        {
            try
            {
                Services.sendLog("Intentando conectarse al servidor: " + ConfigApp.SQL_Server, Services.errorType.InformationMessage);
                string h = "Data Source=" + ConfigApp.SQL_Server + ";Database=" + ConfigApp.SQL_DataBase + ";User Id=" + ConfigApp.SQL_User + ";Password=" + ConfigApp.SQL_Password + ";";
                Services.sendLog(h, Services.errorType.InformationMessage);

                sqlConnection = new SqlConnection(h);

                sqlConnection.Open();
                Services.sendLog("Conexion establecida con el servidor " + ConfigApp.SQL_Server, Services.errorType.InformationMessage);


            }
            catch (SqlException sqlExc)
            {
                throw (sqlExc);
            }
        }

        public string getConsecutivo()
        {

            try
            {
                // this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "CONSECUTIVOVACACIONES";


               

               sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure 
               SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure   
               if (reader.HasRows)
               {
                   reader.Read();
                   return reader.GetString(0);
               }
               else { return ""; }

              
            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        }

        public void CloseConnection()
        {
            try
            {
                sqlConnection.Close();
                sqlConnection.Dispose();
            }
            catch (SqlException sqlExc)
            {
                throw (sqlExc);
            }
        }

        public bool UPDATE_State(string requestID, string state)
        {

            try
            {
                this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                //sqlCommand.CommandTimeout = Convert.ToInt32(Configuration.topQuery) * (60000);

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "ACTUALIZAR_SOLICITUD";


                SqlParameter objID = sqlCommand.Parameters.Add("@IDSOLICITUD", SqlDbType.VarChar);
                objID.Direction = ParameterDirection.Input;
                objID.Value = requestID;


                SqlParameter HISTORICO = sqlCommand.Parameters.Add("@ESTADO_SOLICITUD", SqlDbType.VarChar);
                HISTORICO.Direction = ParameterDirection.Input;
                HISTORICO.Value = state;

                int result = sqlCommand.ExecuteNonQuery();
             
                    this.CloseConnection();

                return true;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        
        }
        public bool SQLUpdate(string objectID, string version, string historico, bool openConnection)
        {
            try
            {
                if (openConnection)
                    this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                //sqlCommand.CommandTimeout = Convert.ToInt32(Configuration.topQuery) * (60000);

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                //sqlCommand.CommandText = Configuration.updateCommand;


                SqlParameter objID = sqlCommand.Parameters.Add("@sOBJECT_ID", SqlDbType.VarChar);
                objID.Direction = ParameterDirection.Input;
                objID.Value = objectID;

                SqlParameter VERSION_WORKITEM = sqlCommand.Parameters.Add("@sVersion_Workitem", SqlDbType.VarChar);
                VERSION_WORKITEM.Direction = ParameterDirection.Input;
                VERSION_WORKITEM.Value = version;

                SqlParameter HISTORICO = sqlCommand.Parameters.Add("@sHistorico", SqlDbType.VarChar);
                HISTORICO.Direction = ParameterDirection.Input;
                HISTORICO.Value = historico;

                int result = sqlCommand.ExecuteNonQuery();
                if (openConnection)
                    this.CloseConnection();

                return true;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        }

        public int SQLInsertSolicitud(string idSolicitud,string usuario,string empleado,int tDiasDisponibles,
            int tDiasSolicitados,string fechaInicio, string fechaFin, string fechaRegistro, string fechaWFL, string estado, string aprobador1, string aprobador2, string aprobador3,bool openConnection)
        {
            try
            {
                if (openConnection)
                    this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "INSERTAR_SOLICITUD";


                SqlParameter objID = sqlCommand.Parameters.Add("@sIDSOLICITUD", SqlDbType.VarChar);
                objID.Direction = ParameterDirection.Input;
                objID.Value = idSolicitud;

                SqlParameter USUARIO = sqlCommand.Parameters.Add("@sUSUARIO", SqlDbType.VarChar);
                USUARIO.Direction = ParameterDirection.Input;
                USUARIO.Value = usuario;

                SqlParameter EMPLEADO = sqlCommand.Parameters.Add("@sEMPLEADO", SqlDbType.VarChar);
                EMPLEADO.Direction = ParameterDirection.Input;
                EMPLEADO.Value = empleado;

                SqlParameter TDIASDISPONIBLES = sqlCommand.Parameters.Add("@sTDIASDISPONIBLES", SqlDbType.Int);
                TDIASDISPONIBLES.Direction = ParameterDirection.Input;
                TDIASDISPONIBLES.Value = tDiasDisponibles;

                SqlParameter TDIASSOLICITADOS = sqlCommand.Parameters.Add("@sTDIASSOLICITADOS", SqlDbType.Int);
                TDIASSOLICITADOS.Direction = ParameterDirection.Input;
                TDIASSOLICITADOS.Value = tDiasSolicitados;

                SqlParameter FECHA_INICIO = sqlCommand.Parameters.Add("@sFECHA_INICIO", SqlDbType.VarChar);
                FECHA_INICIO.Direction = ParameterDirection.Input;
                FECHA_INICIO.Value = fechaInicio;

                SqlParameter FECHA_FIN = sqlCommand.Parameters.Add("@sFECHA_FIN", SqlDbType.VarChar);
                FECHA_FIN.Direction = ParameterDirection.Input;
                FECHA_FIN.Value = fechaFin;

                SqlParameter FECHA_REGISTRO = sqlCommand.Parameters.Add("@sFECHA_REGISTRO", SqlDbType.VarChar);
                FECHA_REGISTRO.Direction = ParameterDirection.Input;
                FECHA_REGISTRO.Value = fechaRegistro;

                SqlParameter FECHA_ENVIO_WF = sqlCommand.Parameters.Add("@sFECHA_ENVIO_WF", SqlDbType.VarChar);
                FECHA_ENVIO_WF.Direction = ParameterDirection.Input;
                FECHA_ENVIO_WF.Value = fechaWFL;

                SqlParameter ESTADO_SOLICITUD = sqlCommand.Parameters.Add("@sESTADO_SOLICITUD", SqlDbType.VarChar);
                ESTADO_SOLICITUD.Direction = ParameterDirection.Input;
                ESTADO_SOLICITUD.Value = estado;

                int result = sqlCommand.ExecuteNonQuery();
                if (openConnection)
                    this.CloseConnection();

                return result;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }
        }

        public int TotalAprovees(string employeeID)
        {
            try
            {
                // this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "TOTAL_APROBADORES";


                SqlParameter parameter = sqlCommand.Parameters.Add("@sCEDULA", SqlDbType.VarChar);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = employeeID;

               SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure   
               if (reader.HasRows)
               {
                   reader.Read();
                   return Convert.ToInt32(reader.GetString(0));
               }
               else { return 0; }

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
               
                return 0;
            }
        }

        public void UpdateHistorico(string requestID, string fecha, string tipoTrans, string employeeID, bool notified, string state, string comment)
        {
            throw new System.NotImplementedException();
        }

        public SqlDataReader SearchByBoss(string bossID)
        {
            try
            {
                 this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.CommandText = "SEARCHBYBOSS";


                SqlParameter parameter = sqlCommand.Parameters.Add("@BOSSID", SqlDbType.VarChar);
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = bossID;

                SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); //retorna el resultado del store procedure   
                return reader;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                Services.sendLog("Ocurrio un error de base de datos " + ex.Message , Services.errorType.DBError);
                return null;
            }
            //throw new System.NotImplementedException();
        }

        public bool CommentRequest(string requestID, string comment, bool openConnection)
        {

            try
            {
                if (openConnection)
                    this.OpenConnection();
                SqlCommand sqlCommand = new SqlCommand();
                //sqlCommand.CommandTimeout = Convert.ToInt32(Configuration.topQuery) * (60000);

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.StoredProcedure;
                //sqlCommand.CommandText = Configuration.updateCommand;


                SqlParameter IDSOLICITUD = sqlCommand.Parameters.Add("@IDSOLICITUD", SqlDbType.VarChar);
                IDSOLICITUD.Direction = ParameterDirection.Input;
                IDSOLICITUD.Value = requestID;

                SqlParameter COMMENT = sqlCommand.Parameters.Add("@COMMENT", SqlDbType.VarChar);
                COMMENT.Direction = ParameterDirection.Input;
                COMMENT.Value = comment;

               

                int result = sqlCommand.ExecuteNonQuery();
                if (openConnection)
                    this.CloseConnection();

                return true;

            }
            catch (SqlException ex)
            {
                this.CloseConnection();
                throw (ex);
            }

            
        }
    }
}
